package James;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import java.sql.*;

public class TableViewer extends JPanel implements ActionListener
{
	JFrame frame = null;
	PreparedStatement prepStmt = null;
	Connection myConn = null;
	ResultSet rs = null;
	
	JPanel mainP = null, buttonP = null;
	
	JRadioButton fortyMinusB, fortyPlusB;
	JButton nextB;
	
	JLabel l1, l2,l3,l4,l5,l6;
	JTextField jtf1,jtf2,jtf3,jtf4,jtf5,jtf6;
	
	//constructor
	public TableViewer()
	{
		//Connection, prepared statement and rs
		//*************************************
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			myConn = DriverManager.getConnection("jdbc:mysql://localhost/StansLibrary",
																			"", "");
			System.out.println("Connection opened successfully...");
			prepStmt = myConn.prepareStatement("SELECT empId, surname, firstName, salary, " +
					"birth, (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<" +
					"RIGHT(birth,5)) AS age FROM employeeTable WHERE YEAR(birth) BETWEEN ? AND ?");
		}
		catch(ClassNotFoundException cnfe)
		{
			System.out.println(cnfe.getMessage());
			cnfe.printStackTrace();
		}
		catch(SQLException sqle)
		{
			System.out.println(sqle.getMessage());
			sqle.printStackTrace();
		}
		
		//GUI
		//***
		mainP = new JPanel(new GridLayout(6,2));
		l1 = new JLabel("Employee ID:");
		l2 = new JLabel("Surname:");
		l3 = new JLabel("First Name:");
		l4 = new JLabel("Date of Birth:");
		l5 = new JLabel("Salary:");
		l6 = new JLabel("Age:");
		
		jtf1 = new JTextField(20);
		jtf2 = new JTextField(20);
		jtf3 = new JTextField(20);
		jtf4 = new JTextField(20);
		jtf5 = new JTextField(20);
		jtf6 = new JTextField(20);
		
		buttonP = new JPanel(new GridLayout(2,1));
		fortyMinusB = new JRadioButton("<40");
		fortyMinusB.addActionListener(this);
		fortyPlusB = new JRadioButton(">40");
		fortyPlusB.addActionListener(this);
		nextB = new JButton("Next");
		nextB.addActionListener(this);
		buttonP.add(fortyMinusB);
		buttonP.add(fortyPlusB);
		buttonP.add(nextB);
		ButtonGroup bg = new ButtonGroup();
		bg.add(fortyMinusB);
		bg.add(fortyPlusB);
		
		mainP.add(l1); mainP.add(jtf1);
		mainP.add(l2); mainP.add(jtf2);
		mainP.add(l3); mainP.add(jtf3);
		mainP.add(l4); mainP.add(jtf4);
		mainP.add(l5); mainP.add(jtf5);
		mainP.add(l6); mainP.add(jtf6);
		
		this.setLayout(new BorderLayout());
		this.add(mainP, BorderLayout.CENTER);
		this.add(buttonP, BorderLayout.SOUTH);
		
		frame = new JFrame("Table Viewer");
		frame.setSize(400,400);
		frame.addWindowListener(
			new WindowAdapter()
			{
				public void windowClosing(WindowEvent we)
				{
					try
					{
						myConn.close();
						System.out.println("Connection closed.");
					}
					catch(SQLException sqle)
					{
						System.out.print(sqle.getMessage());
					}
					System.exit(0);
				}
			});
		frame.getContentPane().add(this, BorderLayout.CENTER);
		frame.setVisible(true);
	}//end of Constructor
	
	public void showRow()
	{
		try
		{
			jtf1.setText(rs.getString("empId"));
			jtf2.setText(rs.getString("surname"));
			jtf3.setText(rs.getString("firstName"));
			jtf4.setText(rs.getString("birth"));
			jtf5.setText(rs.getString("salary"));
			jtf6.setText(rs.getString("age"));
		}
		catch(SQLException se)
		{
			se.printStackTrace();
			se.printStackTrace();
		}

	}
	
	public void actionPerformed(ActionEvent ae)
	{
		try
		{
			Object source = ae.getSource();
			if(source == fortyMinusB)
			{
				prepStmt.setInt(1, 1967);
				prepStmt.setInt(2, 2007);
				rs = prepStmt.executeQuery();
				rs.first();
				showRow();
			}
			if(source == fortyPlusB)
			{
				prepStmt.setInt(1, 1800);
				prepStmt.setInt(2, 1967);
				rs = prepStmt.executeQuery();
				rs.first();
				showRow();
			}
			if(source == nextB)
			{
				if(rs.isLast())
					{
						rs.beforeFirst();
					}
					rs.next();
					showRow();
			}
		}
		catch(SQLException sqle)
		{
			System.out.println(sqle.getMessage());
			sqle.printStackTrace();
		}
	}
	
	public static void main(String [] args)
	{
		new TableViewer();
	}
}
